Load worksheet
Purpose of this building block
Insert data from a worksheet into documents or emails.
1 variables
3 variables
${fileId}
${current{}.rowNumber}
${current{}.rowIndex}
${current{}.date}
Possible use cases
- Create Invoices
- Compile and send reports
- Perform calculations and updates on worksheets
Configuration
Click on the bold text in the title of the building block to open the configuration dialog.
Edit
Click on the edit icon to open the selected Google Sheet in a new browser tab or window.
Reload
Click on the reload icon to scan the sheet again to track down added or removed worksheets or columns.
If you want to generate different documents for each row in your sheet or whenever a user adds a new row by submitting a form, please use the Load row(s) building block instead!
When dragging this block into the flow you can either select an existing spreadsheet from Google Drive or you can create a new one. If you choose to select an existing sheet, a file picker dialog will help you to find the sheet on your Google Drive. Creating a new spreadsheet will open the new sheet in a new browser tab.
After selecting the Google Sheet a dialog will allows you to configure which data to load. In the dialog you can select the worksheet from your Google Sheet to be loaded. If you want to insert data from multiple worksheets, simply add more building blocks of this kind and select the different worksheets.
Select the row that contains the column names as your header row. In most cases the header row is the first row. If you have a complex worksheet with multiple sections you may want to select a different header row. You can then select the first row to be loaded and the maximum number of rows. This will allow you to embed just a selected set of rows into your documents. Specify a query if you only want to load rows matching a given criteria.
The building block shows all the data found in the Google Sheet on the right-hand side. All of these variables will get a green sheet icon. You can find all columns of all worksheets as variables. The three lines on the right of the variable indicate that each variable represents a list of values: they contain the data from all rows for each column.
When settings the maximum numbers of rows to load to one you will get variables in curly braces as they just hold a single value - the value of the column of the loaded row. You will be able to simply access the different columns as simple variables. This features comes in handy if you want to load just one row based on a query and simply want to use the columns of the loaded row as variables.
Use the simplified column names as you'll find them in the output section of the building block. If you for example have a column with the header My Second Column, the simplified name will be mysecondcolumn
When building your query you have to use a different depending on the contents of your column.
If the column that you want to use in your query contains numbers, you can use statements like age>20 , age=20 , age!=10 (prints all rows where the contents of column age does not equal 10) or age<30.
Of course you can use variables in the query itself, so if you have a variable called mynumber that for example contains a number that a user has entered into a form, you can build queries like age>18 and age<${mynumber}.
If the column contains strings, you'll have to wrap the value in quotes like this: name="olli" or name="${mytext}" and name!="daniel".
You can build complex queries from multiple columns using and and or operators: age>18 and age<${mynumber} or name="${mytext}".
Useful combinations
Embed live tables into PDF documents
calendarWeek
branch
revenue
calendarWeek
Revenue overview PDF
branch
revenue
Revenue data of different branches in maintained in a worksheet. Branches may enter their information directly into the sheet or use a form.
Using the Google Docs to PDF building block you can generate a PDF containing current revenue data. In the generated document bases on a template in which you can easily embed the worksheet data as a table and add custom texts and/or formatting.
Load assorted rows into table
calendarWeek
branch
revenue
calendarWeek
Branch revenue overview
branch
revenue
Branch revenue overview
Branch folder in Drive
In case you need to create separate reports for individual segments just add a query to the Load worksheet building block. Thereby you can filter any available column on the desired values. The extract will only contain matching rows.
Use Branch Report wizard.
Send email in case of critical data
calendarWeek
branch
orders
calendarWeek
Critical order level
branch
orders
Critical order level
Alert email
For special cases you can also add a query to the Load worksheet building block and generate reports with critical information. Such data can be included into a PDF overview and emailed to a person in charge right away.
VAT rate look-up from worksheet.
Related examples
Go to Subflow building block examples.
Questions and Feedback
If you have any comments on this guide, feel free to add them right to the Google document that we are using to create this site.
If you are not yet member of the Ultradox community on Google+, please join now to get updates from our end or to provide feedback, bug reports or discuss with other users.
Last Updated:
